/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;
  
import entities.ProductCategories;
import factory.DBConnectionFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author theworldiwant
 */
public class MySQLProdCatDAO extends ProductCatDAO{
    @Override
    public int getCurId(){
            int ctr=0;
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt2;
        try {
            pstmt2 = conn.prepareStatement("select id from productCategories");
            ResultSet rs=pstmt2.executeQuery();
            while(rs.next())
                ctr=rs.getInt("id");
        } catch (SQLException ex) {
            Logger.getLogger(MySQLProdCatDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
           
            
        return ctr;
    }
     
     @Override
    public void addProdCat(ProductCategories pc) {
         try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("insert into productCategories(id,name,description,subCategoryId) values (?,?,?,?) ");
            pstmt.setInt(1, getCurId()+1);
            pstmt.setString(2, pc.getName());
            pstmt.setString(3, pc.getDescription());
            
            pstmt.setInt(4, pc.getSubCategoryId());
            pstmt.executeUpdate();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(MySQLProdCatDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
 
    @Override
    public void deleteProdCat(ProductCategories pc) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("DELETE FROM `webdeve`.`productcategories` WHERE `id`='"+pc.getId()+"';");
            
            pstmt.executeUpdate();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(MySQLProdCatDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public LinkedList<ProductCategories> arrangeAlp() {
         LinkedList<ProductCategories> list = new LinkedList<ProductCategories>() {};
           
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement(" SELECT DISTINCT name, description, subcategoryId FROM productcategories WHERE name<>'All' ORDER BY name;");
            ProductCategories pc2 = new ProductCategories();
            ResultSet rs=pstmt.executeQuery();
            while(rs.next()){
                pc2 = new ProductCategories();
                
                pc2.setName(rs.getString("name"));
                pc2.setDescription(rs.getString("description"));
                pc2.setSubCategoryId(rs.getInt("subcategoryId"));
                
                list.add(pc2);
            }
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(MySQLProdCatDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }

    @Override
    public int getCatIdGenre(String str) {
        // select id from productcategories where name='all';
          
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement(" select id from productcategories where name='"+str+"';");
             ResultSet rs=pstmt.executeQuery();
             int x=0;
             while(rs.next())
                x=    rs.getInt(1);
            conn.close();
            return x;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLProdCatDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
        
    }
 
}
